import pandas as pd
import xlwings as xw    
import os

# 将每个文件中的数据，按照某一列分类，统计

app = xw.App(visible=False)
for fname in os.listdir('.'):
    if fname.endswith('.xlsx') and not fname.startswith('~$'):
        wb = app.books.open(fname)
        sheet = wb.sheets[0]
        # df = pd.DataFrame(sheet.used_range.value)
        df = sheet.range('A1').options(pd.DataFrame, expand='table').value
        # 根据某一列分组后，统计某一列的总和
        result = df.groupby(by=df.columns[0]).sum()
        # 将结果保存到当前sheet的指定区域
        sheet.range('A100').value = result
        wb.save()
        wb.close()
app.quit()